// ==================================================================================================
// Copyright(C) 2006 NCS Pte. Ltd. All rights reserved.
//
// SYSTEM NAME	: iConnect .Net Application Framework
// COMPONENT ID	: NCS.IConnect.Helpers.Data.CommandWrapper
// COMPONENT DESC:		
//
// CREATED DATE/BY:		12 July 2005 / Lu DingSheng
//
// REVISION HISTORY:	DATE/BY			SR#/CS/PM#/OTHERS		DESCRIPTION OF CHANGE
// ==================================================================================================

using System;
using System.Data;
using System.Text;
using System.Data.OracleClient;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;

using NCS.IConnect.Transactions;
using NCS.IConnect.Helpers.Data.Configuration;
using NCS.IConnect.Helpers.Data.Properties;
 
namespace NCS.IConnect.Helpers.Data
{
    /// <summary>
    /// Represents a wrapper for DbCommand types.
    /// </summary>
    public class CommandWrapper
    {
        #region Private const and virable
        private const string virtualTableName = "EXECUTE";

        internal DataSet dataSet;
     
        /// <summary>
        /// The DataSet contains the original value
        /// </summary>
        //internal DataSet originalDataSet;        

        internal Dictionary<string, Dictionary<DbOperationType, DbCommand>> tableCommands = new Dictionary<string, Dictionary<DbOperationType, DbCommand>>();
        
        private Database _db;

        private CommandType _commandType;

        bool _parameterFormatChecked;

        private DataAccessSettings _settings = DataAccessSettings.GetDataAccessSettings();
             
        private const string TableNameFormat = "{TableName}";
        private const string FieldNameFormat = "{DataColumnName}";
              
        private static ParameterCache _parameterCache = new ParameterCache();
        #endregion

        #region Public Indexer
        /// <summary>
        /// Gets the DbCommand
        /// </summary>
        /// <value></value>
        /// <exception cref="ArgumentNullException">Input DataTable is null.</exception>
        public IDbCommand this[DataTable dataTable, DbOperationType operationType]
        {
            get
            {
                if (dataTable == null) throw new ArgumentNullException("dataTable");

                if (!tableCommands.ContainsKey(dataTable.TableName))
                {
                    return null;
                }
                Dictionary<DbOperationType, DbCommand> commandDic = tableCommands[dataTable.TableName];
                return commandDic[operationType];
            }
        }

        /// <summary>
        /// Gets the select DbCommand.
        /// </summary>
        /// <value></value>
        /// <exception cref="ArgumentNullException">Input DataTable is null.</exception>
        public IDbCommand this[DataTable dataTable]
        {
            get
            {
                return this[dataTable, DbOperationType.Select];               
            }
        }

        /// <summary>
        /// Gets a arrayList which contains all command.
        /// </summary>
        /// <value>The commands.</value>
        public ArrayList Commands
        {
            get
            {
                ArrayList commandAL = new ArrayList();
                Dictionary<DbOperationType, DbCommand> commandDic;
                if (tableCommands.ContainsKey(virtualTableName))
                {
                    commandDic = tableCommands[virtualTableName];
                    if (commandDic.ContainsKey(DbOperationType.Select))
                    {
                        commandAL.Add(commandDic[DbOperationType.Select]);
                    }
                    if (commandDic.ContainsKey(DbOperationType.Delete))
                    {
                        commandAL.Add(commandDic[DbOperationType.Delete]);
                    }
                    if (commandDic.ContainsKey(DbOperationType.Update))
                    {
                        commandAL.Add(commandDic[DbOperationType.Update]);
                    }
                    if (commandDic.ContainsKey(DbOperationType.Insert))
                    {
                        commandAL.Add(commandDic[DbOperationType.Insert]);
                    }

                }
                else
                {
                    foreach (DataTable dataTable in dataSet.Tables)
                    {
                        if (tableCommands.ContainsKey(dataTable.TableName))
                        {
                            commandDic = tableCommands[dataTable.TableName];
                            if (commandDic.ContainsKey(DbOperationType.Select))
                            {
                                commandAL.Add(commandDic[DbOperationType.Select]);
                            }
                            if (commandDic.ContainsKey(DbOperationType.Delete))
                            {
                                commandAL.Add(commandDic[DbOperationType.Delete]);
                            }
                            if (commandDic.ContainsKey(DbOperationType.Update))
                            {
                                commandAL.Add(commandDic[DbOperationType.Update]);
                            }
                            if (commandDic.ContainsKey(DbOperationType.Insert))
                            {
                                commandAL.Add(commandDic[DbOperationType.Insert]);
                            }
                        }
                    }
                }
                return commandAL;
            }
        }
        #endregion
                
        #region Public Method
        /// <summary>
        /// Initializes a new instance of the <see cref="T:CommandWrapper"/> class.
        /// </summary>
        /// <param name="commandWrapperType">For filling or For updating.</param>
        /// <param name="db">The database object</param>
        /// <param name="commandType">Text or StoreProcedure.</param>
        /// <param name="dataSet">The input DataSet.</param>
        /// <param name="commandTexts">The comandtext for every table.</param>
        public CommandWrapper(CommandWrapperType commandWrapperType, Database db, CommandType commandType, DataSet dataSet, Dictionary<string,string> commandTexts)
        {
            this._db = db;
            this.dataSet = dataSet;
            //this.originalDataSet = dataSet;
            this._commandType = commandType;

            switch (commandWrapperType)
            {
                case CommandWrapperType.Fill:
                    {
                        switch (commandType)
                        {
                            case CommandType.Text:
                                {
                                    PrepareSelectCommand4Text(dataSet, commandTexts);
                                    break;
                                }
                            case CommandType.StoredProcedure:
                                {
                                    PrepareSelectCommand4SP(dataSet, commandTexts);
                                    break;
                                }
                        }
                        break;
                    }
                case CommandWrapperType.Update:
                    {                        
                        DataSetHelper.CheckDataSet(dataSet);                        
                        switch (commandType)
                        {
                            case CommandType.Text:
                                {
                                    PrepareUpdateCommand4Text(dataSet);
                                    break;
                                }
                            case CommandType.StoredProcedure:
                                {
                                    PrepareUpdateCommand4SP(dataSet);
                                    break;
                                }
                        }
                        break;
                    }
            }

        }

        /// <summary>
        /// Initializes a new instance of the <see cref="T:CommandWrapper"/> class.
        /// </summary>
        /// <param name="commandWrapperType">For filling or For updating.</param>
        /// <param name="db">The instance of <see cref="Database"/> object.</param>
        /// <param name="commandType">Text or StoreProcedure.</param>
        /// <param name="dataTable">The input DataTable.</param>
        /// <param name="commandText">The comandtext for every table.</param>
        /// <exception cref="ArgumentNullException">Input DataTable is null.</exception>
        public CommandWrapper(CommandWrapperType commandWrapperType, Database db, CommandType commandType, DataTable dataTable, string commandText)
        {
            this._db = db;
            this.dataSet = dataTable.DataSet;
            //this.originalDataSet = dataTable.DataSet;
            this._commandType = commandType;
            
            switch (commandWrapperType)
            {
                case CommandWrapperType.Fill:
                    {
                        switch (commandType)
                        {
                            case CommandType.Text:
                                {
                                    PrepareSelectCommand4Text(dataTable, commandText);
                                    break;
                                }
                            case CommandType.StoredProcedure:
                                {
                                    PrepareSelectCommand4SP(dataTable, commandText);
                                    break;
                                }
                        }
                        break;
                    }
                case CommandWrapperType.Update:
                    {                        
                        DataSetHelper.CheckDataTable(dataTable);                        
                        switch (commandType)
                        {
                            case CommandType.Text:
                                {
                                    PrepareUpdateCommand4Text(dataTable);
                                    break;
                                }
                            case CommandType.StoredProcedure:
                                {
                                    PrepareUpdateCommand4SP(dataTable);
                                    break;
                                }
                        }
                        break;
                    }
            }
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="T:CommandWrapper"/> class.
        /// </summary>
        /// <param name="db">The instance of <see cref="Database"/> object.</param>
        /// <param name="commandType">Text or StoreProcedure.</param>
        /// <param name="commandText">Command Text.</param>
        public CommandWrapper(Database db, CommandType commandType, string commandText)
        {
            this._db = db;
            this._commandType = commandType;

            switch (commandType)
            {
                case CommandType.Text:
                    {
                        PrepareCommand4Text(commandText);
                        break;
                    }
                case CommandType.StoredProcedure:
                    {
                        PrepareCommand4SP(commandText);
                        break;
                    }
            }
        }

        /// <summary>
        /// Gets the command text through table name and operation type.
        /// </summary>
        /// <param name="dataTable">The instance of DataTable.</param>
        /// <param name="operationType">The operation type(Select, Update, Insert, Delete).</param>
        /// <returns>The command text.</returns>
        /// <exception cref="ArgumentNullException">Input DataTable is null.</exception>
        public string GetCommandText(DataTable dataTable, DbOperationType operationType)
        {
            Dictionary<DbOperationType, DbCommand> commandDic;

            if (dataTable == null) throw new ArgumentNullException("dataTable");

            if (tableCommands.TryGetValue(dataTable.TableName, out commandDic))
            {   
                DbCommand dbCommand;
                if (commandDic.TryGetValue(operationType,out dbCommand))
                {
                    return dbCommand.CommandText;
                }             
            }
            return string.Empty;
        }

        /// <summary>
        /// Sets the command text through table name and operation type.
        /// </summary>
        /// <param name="dataTable">The instance of DataTable.</param>
        /// <param name="operationType">The operation type(Select, Update, Insert, Delete).</param>
        /// <param name="commandText">The command text.</param>
        /// <exception cref="ArgumentNullException">Input DataTable is null.</exception>
        public void SetCommandText(DataTable dataTable, DbOperationType operationType, string commandText)
        {
            Dictionary<DbOperationType, DbCommand> commandDic;

            if (dataTable == null) throw new ArgumentNullException("dataTable");

            if (tableCommands.TryGetValue(dataTable.TableName, out commandDic))
            { 
                DbCommand dbCommand;
                if (commandDic.TryGetValue(operationType, out dbCommand))
                {
                    dbCommand.CommandText = commandText;
                    RefreshParameters(dataTable, operationType);
                }             
            }
        }


        /// <summary>
        /// Removes the command text through table name and operation type.
        /// </summary>
        /// <param name="dataTable">The instance of DataTable.</param>
        /// <param name="operationType">The operation type(Select, Update, Insert, Delete).</param>
        /// <exception cref="ArgumentNullException">Input DataTable is null.</exception>
        public void RemoveCommand(DataTable dataTable, DbOperationType operationType)
        {
            Dictionary<DbOperationType, DbCommand> commandDic;

            if (dataTable == null) throw new ArgumentNullException("dataTable");

            if (tableCommands.TryGetValue(dataTable.TableName, out commandDic))
            {                  
                if (commandDic.ContainsKey(operationType))
                {
                    commandDic.Remove(operationType);
                }             
            }
        }


        /// <summary>
        /// Adds input type parameters to the DbCommand which is got through <paramref name="dataTable"/> and <paramref name="operationType"/>.
        /// </summary>
        /// <param name="dataTable">The instance of dataTable, specify null for ExecuteNonQuery and ExecuteScalar.</param>
        /// <param name="parameterValues">The object contains parameter values.</param>    
        public void AddInParameters(DataTable dataTable, params object[] parameterValues)
        {
            Dictionary<DbOperationType, DbCommand> commandDic;
            DbCommand dbCommand;
            ArrayList totalParameterValues = new ArrayList();
            int counter = 0;
            
            //get the command instance for fill method
            if (dataTable != null)
            {
                if (tableCommands.TryGetValue(dataTable.TableName, out commandDic))
                {
                    if (commandDic.TryGetValue(DbOperationType.Select, out dbCommand))
                    {                        
                        CheckCommandType(dbCommand);                                    
                        
                        foreach(DbParameter param in dbCommand.Parameters)
                        {
                            if (param.Direction == ParameterDirection.Output)
                            {
                                totalParameterValues.Add(null);
                            }
                            else if (param.Direction == ParameterDirection.ReturnValue)
                            {
                                continue;
                            }
                            else if (param.Direction == ParameterDirection.InputOutput)
                            {
                                totalParameterValues.Add(DBNull.Value);
                            }
                            else
                            {
                                totalParameterValues.Add(parameterValues[counter]);
                                counter++;
                            }
                        }
                        _db.AssignParameterValues(dbCommand, totalParameterValues.ToArray());
                    }
                }
                else
                {
                    throw new KeyNotFoundException(string.Format(Resources.Culture, Resources.CommandNotFoundForTable, dataTable.TableName));
                }
            }
            //get the command instance for execute method
            else
            {
                if (tableCommands.TryGetValue(virtualTableName, out commandDic))
                {
                    if (commandDic.TryGetValue(DbOperationType.Select, out dbCommand))
                    {                       
                        CheckCommandType(dbCommand);                        
                        _db.AssignParameterValues(dbCommand, parameterValues);
                    }
                }
                else
                {
                    throw new KeyNotFoundException(string.Format(Resources.Culture, Resources.CommandNotFound));
                }
            }
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic")]
        private void CheckCommandType(DbCommand dbCommand)
        {
            if (dbCommand.CommandType == CommandType.Text)
            {
                throw new NotSupportedException(string.Format(Resources.Culture, Resources.CommandTypeNotSupport));
            }
        }

        /// <summary>
        /// Adds a input type parameter to the DbCommand which is got through <paramref name="tableName"/> and <paramref name="operationType"/>.
        /// </summary>
        /// <param name="dataTable">The instance of DataTable.</param>        
        /// <param name="parameterName">The paramter name.</param>
        /// <param name="dbType">The DbType.</param>
        /// <param name="value">The parameter value.</param>        
        public void AddInParameter(DataTable dataTable, string parameterName, DbType dbType, object value)
        {
            Dictionary<DbOperationType, DbCommand> commandDic;
            DbCommand dbCommand;
            //get the command instance for fill method
            if (dataTable != null)
            {
                if (tableCommands.TryGetValue(dataTable.TableName, out commandDic))
                {                    
                    if (commandDic.TryGetValue(DbOperationType.Select, out dbCommand))
                    {
                        if (dbCommand.CommandType == CommandType.Text)
                        {
                            _db.AddInParameter(dbCommand, parameterName, dbType, value);
                        }
                        else
                        {
                            _db.SetParameterValue(dbCommand, parameterName, value);
                        }
                    }
                }
                else
                {                    
                    throw new KeyNotFoundException(string.Format(Resources.Culture, Resources.CommandNotFoundForTable, dataTable.TableName));
                }
            }
            //get the command instance for execute method
            else
            {
                if (tableCommands.TryGetValue(virtualTableName, out commandDic))
                {
                    if (commandDic.TryGetValue(DbOperationType.Select, out dbCommand))
                    {
                        if (dbCommand.CommandType == CommandType.Text)
                        {
                            _db.AddInParameter(dbCommand, parameterName, dbType, value);
                        }
                        else
                        {
                            _db.SetParameterValue(dbCommand, parameterName, value);
                        }
                    }
                }
                else
                {
                    throw new KeyNotFoundException(string.Format(Resources.Culture, Resources.CommandNotFound));
                }
            }
        }


        /// <summary>
        /// Adds a output type parameter to default DbCommand which is got through <paramref name="tableName"/> and <paramref name="operationType"/>.
        /// </summary>
        /// <param name="dataTable">The instance of DataTable.</param>
        /// <param name="parameterName">The paramter name.</param>
        /// <param name="dbType">The DbType of the parameter.</param>
        /// <param name="size">The parameter size.</param>
        /// <exception cref="ArgumentNullException">Input DataTable is null.</exception>
        public void AddOutParameter(DataTable dataTable, string parameterName, DbType dbType, int size)
        {
            Dictionary<DbOperationType, DbCommand> commandDic;

            if (dataTable == null) throw new ArgumentNullException("dataTable");

            if (tableCommands.TryGetValue(dataTable.TableName, out commandDic))
            {  
                DbCommand dbCommand;
                if (commandDic.TryGetValue(DbOperationType.Select, out dbCommand))
                {                    
                    if (dbCommand.CommandType == CommandType.Text)
                    {
                        _db.AddOutParameter(dbCommand, parameterName, dbType, size);
                    }
                    else
                    {
                        _db.SetParameterValue(dbCommand, parameterName, DBNull.Value);
                    }
                }
             }
        }


        /// <summary>
        /// Gets the parameter value.
        /// </summary>
        /// <param name="dataTable">The instance of DataTable.</param>
        /// <param name="operationType">The operation type(Select, Update, Insert, Delete)</param>
        /// <param name="parameterName">The parameter name</param>
        /// <returns>The parameter value</returns>
        /// <exception cref="ArgumentNullException">Input DataTable is null.</exception>
        public object GetParameterValue(DataTable dataTable, DbOperationType operationType, string parameterName)
        {
            Dictionary<DbOperationType, DbCommand> commandDic;

            if (dataTable == null) throw new ArgumentNullException("dataTable");

            if (tableCommands.TryGetValue(dataTable.TableName, out commandDic))
            {
                DbCommand dbCommand;
                if (commandDic.TryGetValue(operationType, out dbCommand))
                {
                    return _db.GetParameterValue(dbCommand, parameterName);
                }             
            }
            return null;
        }


        /// <summary>
        /// Gets the parameter return value from the default DbCommand(the first one).
        /// </summary>
        /// <param name="parameterName">Name of the parameter.</param>
        /// <returns>The parameter value</returns>
        public object GetParameterValue(string parameterName)
        {
            foreach (Dictionary<DbOperationType,DbCommand> i in tableCommands.Values)
            {
                foreach (DbCommand j in i.Values)
                {
                    return _db.GetParameterValue(j ,parameterName);
                }
            }
            return null;
        }

        /// <summary>
        /// Adds the Oracle cursor parameter.
        /// </summary>
        /// <param name="dataTable">The instance of DataTable.</param>
        /// <param name="parameterName">Name of the parameter.</param>
        /// <exception cref="ArgumentNullException">Input DataTable is null.</exception>
        public void AddOracleCursorParameter(DataTable dataTable, string parameterName)
        {
            Dictionary<DbOperationType, DbCommand> commandDic;

            if (dataTable == null) throw new ArgumentNullException("dataTable");

            if (tableCommands.TryGetValue(dataTable.TableName, out commandDic))
            {
                DbCommand dbCommand;
                if (commandDic.TryGetValue(DbOperationType.Select, out dbCommand))
                {
                    ((OracleDatabase)_db).AddParameter((OracleCommand)dbCommand, parameterName, OracleType.Cursor, 0, ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Current, "");
                }              
            }
        }

        /// <summary>
        /// Clears the parameter cache.
        /// </summary>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic")]
        public void ClearParameterCache()
        {
            _parameterCache.Clear();
        }
        #endregion

        #region Private Method
        /// <summary>
        /// Refreshs the parameter collection, get the parameters from db.
        /// </summary>
        /// <param name="dataTable">The instance of DataTable.</param>
        /// <param name="operationType">The operation type(Select, Update, Insert, Delete).</param>
        /// <exception cref="ArgumentNullException">Input DataTable is null.</exception>
        private void RefreshParameters(DataTable dataTable, DbOperationType operationType)
        {
            if (this._commandType == CommandType.StoredProcedure)
            {
                Dictionary<DbOperationType, DbCommand> commandDic;

                if (dataTable == null) throw new ArgumentNullException("dataTable");

                if (tableCommands.TryGetValue(dataTable.TableName, out commandDic))
                {
                    DbCommand dbCommand;
                    if (commandDic.TryGetValue(operationType, out dbCommand))
                    {
                        dbCommand.Parameters.Clear();                        
                        _parameterCache.SetParameters(dbCommand, _db);                        
                        AssignParameters(dataTable, dbCommand.Parameters);
                    }
                }
            }
        }

        /// <summary>
        /// Gets the name of the procedure.
        /// </summary>
        /// <param name="dataTable">The instance of DataTable.</param>
        /// <param name="operationType">Type of the operation.</param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException">Input DataTable is null.</exception>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic")]
        private string GetProcedureName(DataTable dataTable, DbOperationType operationType)
        {
            if (dataTable == null) throw new ArgumentNullException("dataTable");

            string spName = string.Empty;
            string formatName = string.Empty;
            string regexString = "^" +DataAccessConfigurationView.DataTableMapping + "$";
            regexString = regexString.Replace(TableNameFormat, @"(\S*)");

            Regex regex = new Regex(regexString, RegexOptions.IgnoreCase);
            Match match = regex.Match(dataTable.TableName);
            if (match.Success)
            {
                GroupCollection gc = match.Groups;
                if (gc.Count > 1)
                {
                    formatName = gc[1].Value;
                }
            }
            if (String.IsNullOrEmpty(formatName))
            {
                throw new Exception(string.Format(Resources.Culture, Resources.TableNameUnFit, dataTable.TableName));
            }

            switch (operationType)
            {
                case DbOperationType.Select:
                    {
                        spName = DataAccessConfigurationView.SelectProcedureFormat.Replace(TableNameFormat, formatName);
                        break;
                    }
                case DbOperationType.Delete:
                    {
                        spName = DataAccessConfigurationView.DeleteProcedureFormat.Replace(TableNameFormat, formatName);
                        break;
                    }
                case DbOperationType.Update:
                    {
                        spName = DataAccessConfigurationView.UpdateProcedureFormat.Replace(TableNameFormat, formatName);
                        break;
                    }
                case DbOperationType.Insert:
                    {
                        spName = DataAccessConfigurationView.InsertProcedureFormat.Replace(TableNameFormat, formatName);
                        break;
                    }
            }
            return spName;
        }

        /// <summary>
        /// Gets the name of the column.
        /// </summary>
        /// <param name="parameterName">Name of the parameter.</param>
        /// <returns></returns>
        private string GetColumnName(string parameterName)
        {
            this.CheckParameterFormat();

            Regex regex;            
            string columnName = string.Empty;
            
            //If the parameter format for current value is equal to field name, match the format for original value first   
            // e.g., o_version_no will match the related format for <add name="Current" format="{DataColumnName}" />
            if (DataAccessConfigurationView.CurrentParameterFormat == FieldNameFormat)
            {
                regex = new Regex(this.GetRegexPattern(false), RegexOptions.IgnoreCase);
                columnName = this.MatchColumnName(regex, parameterName);
                if (!string.IsNullOrEmpty(columnName))
                {
                    return columnName;
                }

                regex = new Regex(this.GetRegexPattern(true), RegexOptions.IgnoreCase);
                columnName = this.MatchColumnName(regex, parameterName);
                if (!string.IsNullOrEmpty(columnName))
                {
                    return columnName;
                }
            }
            else
            {
                regex = new Regex(this.GetRegexPattern(true), RegexOptions.IgnoreCase);
                columnName = this.MatchColumnName(regex, parameterName);
                if (!string.IsNullOrEmpty(columnName))
                {
                    return columnName;
                }

                regex = new Regex(this.GetRegexPattern(false), RegexOptions.IgnoreCase);
                columnName = this.MatchColumnName(regex, parameterName);
                if (!string.IsNullOrEmpty(columnName))
                {
                    return columnName;
                }
            }
            return string.Empty;
        }

        /// <summary>
        /// Needs the orignal version.
        /// </summary>
        /// <param name="parameterName">Name of the parameter.</param>
        /// <returns></returns>
        private bool NeedOrignalVersion(string parameterName)
        {
            string regexString = this.GetRegexPattern(false);
            Regex regex = new Regex(regexString, RegexOptions.IgnoreCase);
            Match match = regex.Match(parameterName);
            if (match.Success)
            {
                GroupCollection gc = match.Groups;
                if (gc.Count > 1)
                {
                    return true;
                }
            }
            return false;

        }

        /// <summary>
        /// Gets the regex pattern.
        /// </summary>
        /// <param name="currentValue">current value of DataColumn.</param>
        /// <returns></returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic")]
        private string GetRegexPattern(bool currentValue)
        {
            //parameter token for different database. ":"for oracle, "@"for sql server
            string parameterTokenPattern = "^[:@]{0,1}";
            string regexString = string.Empty;

            if (currentValue)
            {
                regexString = parameterTokenPattern + DataAccessConfigurationView.CurrentParameterFormat + "$";
            }            
            else
            {
                regexString = parameterTokenPattern + DataAccessConfigurationView.OriginalParameterFormat + "$";
            }
            regexString = regexString.Replace(FieldNameFormat, @"(\S*)");
            return regexString;
        }

        /// <summary>
        /// Matches the name of the column.
        /// </summary>
        /// <param name="regex">The regex.</param>
        /// <param name="parameterName">Name of the parameter.</param>
        /// <returns></returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic")]
        private string MatchColumnName(Regex regex, string parameterName)
        {
            Match match = regex.Match(parameterName);
            if (match.Success)
            {
                GroupCollection gc = match.Groups;
                if (gc.Count > 1)
                {
                    return DataAccessConfigurationView.DataColumnMapping.Replace(FieldNameFormat, gc[1].Value);
                }
                else
                {
                    return string.Empty;
                }
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// Checks the parameter format.
        /// </summary>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes")]
        private void CheckParameterFormat()
        {
            if (!_parameterFormatChecked)
            {
                //The parameter format for current value and original value cannot be the same
                if (DataAccessConfigurationView.CurrentParameterFormat == DataAccessConfigurationView.OriginalParameterFormat)
                {
                    throw new Exception(string.Format(Resources.Culture, Resources.CanNotBeSame));
                }
                //The parameter format for original value cannot be the same as column name
                if (DataAccessConfigurationView.OriginalParameterFormat == FieldNameFormat)
                {
                    throw new Exception(string.Format(Resources.Culture, Resources.InvalidParameterForOriginalValue));
                }
                _parameterFormatChecked = true;
            } 
        }
                
        #region Prepare Select Command
        /// <summary>
        /// Prepares the select command for text.
        /// </summary>
        /// <param name="dataSet">The DataSet.</param>
        /// <param name="commandTexts">The command texts.</param>
        private void PrepareSelectCommand4Text(DataSet dataSet, Dictionary<string,string> commandTexts)
        {
            foreach (DataTable dataTable in dataSet.Tables)
            {
                string commandText;
                if (commandTexts != null && commandTexts.TryGetValue(dataTable.TableName, out commandText))
                {
                    if (commandText.Length != 0)
                    {
                        PrepareSelectCommand4Text(dataTable, commandText);
                    }
                }
                else
                {
                    PrepareSelectCommand4Text(dataTable, string.Empty);
                }
            }
        }

        /// <summary>
        /// Prepares the select command for text.
        /// </summary>
        /// <param name="dataTable">The DataTable.</param>
        /// <param name="commandText">The command text.</param>
        private void PrepareSelectCommand4Text(DataTable dataTable, string commandText)
        {
            DbCommand command;
            if (String.IsNullOrEmpty(commandText))
            {
                StringBuilder selectClause = new StringBuilder("SELECT ");
                // Generate select field list in using the schema of the current table 
                foreach (DataColumn dataColumn in dataTable.Columns)
                {
                    selectClause.Append(dataColumn.ColumnName);
                    selectClause.Append(", ");
                }
                selectClause.Remove(selectClause.Length - 2, 2);
                selectClause.Append(" FROM ");
                selectClause.Append(dataTable.TableName);

                command = _db.GetSqlStringCommand(selectClause.ToString());
            }
            else
            {
                command = _db.GetSqlStringCommand(commandText);
            }

            Dictionary<DbOperationType, DbCommand> commandDic = new Dictionary<DbOperationType, DbCommand>();           
            commandDic.Add(DbOperationType.Select, command);
            tableCommands.Add(dataTable.TableName, commandDic);
        }


        /// <summary>
        /// Prepares the select command for procedure.
        /// </summary>
        /// <param name="dataSet">The DataSet.</param>
        /// <param name="commandTexts">The command texts.</param>
        private void PrepareSelectCommand4SP(DataSet dataSet, Dictionary<string,string> commandTexts)
        {
            if (dataSet == null) throw new ArgumentNullException("dataSet");

            foreach (DataTable dataTable in dataSet.Tables)
            {
                string commandText;
                if (commandTexts != null && commandTexts.TryGetValue(dataTable.TableName, out commandText))
                {
                    if (!String.IsNullOrEmpty(commandText))
                    {
                        PrepareSelectCommand4SP(dataTable, commandText);
                    }
                }
                else
                {
                    PrepareSelectCommand4SP(dataTable, string.Empty);
                }
            }
        }

        /// <summary>
        /// Prepares the select command for procedure.
        /// </summary>
        /// <param name="dataTable">The instance of DataTable.</param>
        /// <param name="commandText">The command text.</param>
        /// <exception cref="ArgumentNullException">Input DataTable is null.</exception>
        private void PrepareSelectCommand4SP(DataTable dataTable, string commandText)
        {
            if (dataTable == null) throw new ArgumentNullException("dataTable");

            DbCommand command;
            if (String.IsNullOrEmpty(commandText))
            {
                string spName = this.GetProcedureName(dataTable, DbOperationType.Select);
                command = _db.GetStoredProcCommand(spName);
            }
            else
            {
                command = _db.GetStoredProcCommand(commandText);                
            }
            _parameterCache.SetParameters(command, _db);

            Dictionary<DbOperationType, DbCommand> commandDic = new Dictionary<DbOperationType, DbCommand>();
            commandDic.Add(DbOperationType.Select, command);
            tableCommands.Add(dataTable.TableName, commandDic);
        }

        #endregion

        #region Prepare Update Command
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Maintainability", "CA1500:VariableNamesShouldNotMatchFieldNames", MessageId = "dataSet")]
        private void PrepareUpdateCommand4Text(DataSet dataSet)
        {
            foreach (DataTable dataTable in dataSet.Tables)
            {
                PrepareUpdateCommand4Text(dataTable);
            }
        }

        /// <summary>
        /// Prepares the update command for text.
        /// </summary>
        /// <param name="dataTable">The DataTable.</param>
        private void PrepareUpdateCommand4Text(DataTable dataTable)
        {
            Dictionary<DbOperationType, DbCommand> commandDic = new Dictionary<DbOperationType, DbCommand>();
            StringBuilder sqlClause;
            DbCommand command;
            string parameterName = string.Empty;
                        
            if (dataTable.Select(null, null, DataViewRowState.ModifiedCurrent).Length > 0)
            {
                command = _db.GetSqlStringCommand("Update aaa set a=1");

                sqlClause = new StringBuilder("UPDATE ");
                sqlClause.Append(dataTable.TableName);
                sqlClause.Append(" SET ");

                foreach (DataColumn dataColumn in dataTable.Columns)
                {    
                    if (dataColumn.AutoIncrement) continue;

                    parameterName = _db.BuildParameterName("p_" + dataColumn.ColumnName);
                    if (AttachParameter(command, parameterName, DataRowVersion.Current, dataColumn))
                    {
                        sqlClause.Append(dataColumn.ColumnName);
                        sqlClause.Append("=");
                        sqlClause.Append(parameterName);
                        sqlClause.Append(", ");
                    }

                }
                sqlClause.Remove(sqlClause.Length - 2, 2);

                if (dataTable.PrimaryKey != null && dataTable.PrimaryKey.Length > 0)
                {
                    sqlClause.Append(" WHERE 1=1 ");
                    string columnName = string.Empty;
                    for (int i = 0; i < dataTable.PrimaryKey.Length; i++)
                    {
                        parameterName = _db.BuildParameterName("o_" + dataTable.PrimaryKey[i].ColumnName);
                        sqlClause.Append(" AND ");
                        sqlClause.Append(dataTable.PrimaryKey[i].ColumnName);
                        sqlClause.Append("= ");
                        sqlClause.Append(parameterName);
                        AttachParameter(command, parameterName, DataRowVersion.Original, dataTable.PrimaryKey[i]);
                    }
                    if (dataTable.Columns.Contains(_settings.SystemField.Get(SystemField.VersionNo).FieldName))
                    {
                        parameterName = _db.BuildParameterName("o_" + _settings.SystemField.Get(SystemField.VersionNo).FieldName);
                        sqlClause.Append(" AND ");
                        sqlClause.Append(_settings.SystemField.Get(SystemField.VersionNo).FieldName);
                        sqlClause.Append("= ");
                        sqlClause.Append(parameterName);
                        AttachParameter(command, parameterName, DataRowVersion.Original, dataTable.Columns[_settings.SystemField.Get(SystemField.VersionNo).FieldName]);
                    }
                }

                command.CommandText = sqlClause.ToString();
                commandDic.Add(DbOperationType.Update, command);
            }

            if (dataTable.Select(null, null, DataViewRowState.Deleted).Length > 0)
            {                
                command = _db.GetSqlStringCommand("Update aaa set a=1");

                sqlClause = new StringBuilder("DELETE FROM ");
                sqlClause.Append(dataTable.TableName);

                if (dataTable.PrimaryKey != null && dataTable.PrimaryKey.Length > 0)
                {
                    sqlClause.Append(" WHERE 1=1 ");
                    string columnName = string.Empty;
                    for (int i = 0; i < dataTable.PrimaryKey.Length; i++)
                    {
                        parameterName = _db.BuildParameterName("o_" + dataTable.PrimaryKey[i].ColumnName);
                        sqlClause.Append(" AND ");
                        sqlClause.Append(dataTable.PrimaryKey[i].ColumnName);
                        sqlClause.Append("= ");
                        sqlClause.Append(parameterName);
                        AttachParameter(command, parameterName, DataRowVersion.Original, dataTable.PrimaryKey[i]);
                    }
                    if (dataTable.Columns.Contains(_settings.SystemField.Get(SystemField.VersionNo).FieldName))
                    {
                        sqlClause.Append(" AND ");
                        sqlClause.Append(_settings.SystemField.Get(SystemField.VersionNo).FieldName);
                        sqlClause.Append("= ");
                        sqlClause.Append(_db.BuildParameterName("o_" + _settings.SystemField.Get(SystemField.VersionNo).FieldName));
                        AttachParameter(command, _db.BuildParameterName("o_" + _settings.SystemField.Get(SystemField.VersionNo).FieldName), DataRowVersion.Original, dataTable.Columns[_settings.SystemField.Get(SystemField.VersionNo).FieldName]);
                    }
                }

                command.CommandText = sqlClause.ToString();
                commandDic.Add(DbOperationType.Delete, command);
            }

            if (dataTable.Select(null, null, DataViewRowState.Added).Length > 0)
            {                
                command = _db.GetSqlStringCommand("Update aaa set a=1");

                sqlClause = new StringBuilder("INSERT INTO ");
                sqlClause.Append(dataTable.TableName);
                sqlClause.Append("(");

                StringBuilder valueClause = new StringBuilder(" VALUES(");
                StringBuilder outputClause = new StringBuilder();
                foreach (DataColumn dataColumn in dataTable.Columns)
                {
                    if (dataColumn.AutoIncrement)
                    {
                        parameterName = _db.BuildParameterName("o_" + dataColumn.ColumnName);
                        AttachAutoIncrementParameter(command, parameterName, dataColumn);
                        outputClause.Append(" SET ");
                        outputClause.Append(parameterName);
                        outputClause.Append("=SCOPE_IDENTITY();");
                    }
                    else
                    {
                        parameterName = _db.BuildParameterName("p_" + dataColumn.ColumnName);
                        if (AttachParameter(command, parameterName, DataRowVersion.Current, dataColumn))
                        {
                            sqlClause.Append(dataColumn.ColumnName);
                            sqlClause.Append(", ");

                            valueClause.Append(parameterName);
                            valueClause.Append(", ");
                        }
                    }

                }

                sqlClause.Remove(sqlClause.Length - 2, 2);
                valueClause.Remove(valueClause.Length - 2, 2);

                sqlClause.Append(")");
                sqlClause.Append(valueClause);
                sqlClause.Append(");");
                sqlClause.Append(outputClause);

                command.CommandText = sqlClause.ToString();
                commandDic.Add(DbOperationType.Insert, command);
            }
            tableCommands.Add(dataTable.TableName, commandDic);
        }


        /// <summary>
        /// Prepares the update command for procedure.
        /// </summary>
        /// <param name="dataSet">The DataSet.</param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Maintainability", "CA1500:VariableNamesShouldNotMatchFieldNames", MessageId = "dataSet")]
        private void PrepareUpdateCommand4SP(DataSet dataSet)
        {
            foreach (DataTable dataTable in dataSet.Tables)
            {
                PrepareUpdateCommand4SP(dataTable);
            }
        }

        /// <summary>
        /// Prepares the update command for procedure.
        /// </summary>
        /// <param name="dataTable">The DataTable.</param>
        private void PrepareUpdateCommand4SP(DataTable dataTable)
        {
            Dictionary<DbOperationType, DbCommand> commandDic = new Dictionary<DbOperationType, DbCommand>();
            string spName = string.Empty;
            DbCommand command;

            if (dataTable.Select(null, null, DataViewRowState.Deleted).Length > 0)
            {
                spName = this.GetProcedureName(dataTable, DbOperationType.Delete);
                command = _db.GetStoredProcCommand(spName);

                _parameterCache.SetParameters(command, _db);
                AssignParameters(dataTable, command.Parameters);
                
                commandDic.Add(DbOperationType.Delete, command);
            }

            if (dataTable.Select(null, null, DataViewRowState.ModifiedCurrent).Length > 0)
            {
                spName = this.GetProcedureName(dataTable, DbOperationType.Update);
                command = _db.GetStoredProcCommand(spName);                
               
                _parameterCache.SetParameters(command, _db);
                AssignParameters(dataTable, command.Parameters);
                
                commandDic.Add(DbOperationType.Update, command);
            }

            if (dataTable.Select(null, null, DataViewRowState.Added).Length > 0)
            {
                spName = this.GetProcedureName(dataTable, DbOperationType.Insert);
                command = _db.GetStoredProcCommand(spName);                
                
                _parameterCache.SetParameters(command, _db);
                AssignParameters(dataTable, command.Parameters);
               
                commandDic.Add(DbOperationType.Insert, command);
            }
            tableCommands.Add(dataTable.TableName, commandDic);
        }

        #endregion

        #region Prepare Command
        /// <summary>
        /// Prepares the command for text.
        /// </summary>
        /// <param name="commandText">The command text.</param>
        private void PrepareCommand4Text(string commandText)
        {
            DbCommand command = _db.GetSqlStringCommand(commandText);
            //			for(int i = 0; i < parameters.Length; i++)
            //			{
            //				_DBCommandWrapper.AddParameter(parameters[i].ParameterName, parameters[i].DbType, parameters[i].Direction,
            //					parameters[i].SourceColumn, parameters[i].SourceVersion, parameters[i].Value);
            //			}

            Dictionary<DbOperationType, DbCommand> commandDic = new Dictionary<DbOperationType, DbCommand>();       
            commandDic.Add(DbOperationType.Select, command);
            tableCommands.Add(virtualTableName, commandDic);
        }

        /// <summary>
        /// Prepares the command for procedure.
        /// </summary>
        /// <param name="commandText">The command text.</param>
        private void PrepareCommand4SP(string commandText)
        {
            DbCommand command = _db.GetStoredProcCommand(commandText);
            _parameterCache.SetParameters(command, _db);
            Dictionary<DbOperationType, DbCommand> commandDic = new Dictionary<DbOperationType, DbCommand>();
            commandDic.Add(DbOperationType.Select, command);

            tableCommands.Add(virtualTableName, commandDic);
        }
        #endregion

        #region common Method  
        /// <summary>
        /// Attaches the parameter.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <param name="parameterName">Name of the parameter.</param>
        /// <param name="dataRowVersion">The data row version.</param>
        /// <param name="dataColumn">The data column.</param>
        /// <returns></returns>
        private bool AttachParameter(DbCommand command, string parameterName, DataRowVersion dataRowVersion, DataColumn dataColumn)
        {
            switch (dataColumn.DataType.FullName)
            {
                case "System.DateTime":
                    _db.AddInParameter(command, parameterName, DbType.DateTime, dataColumn.ColumnName, dataRowVersion);
                    break;
                case "System.String":
                    _db.AddInParameter(command, parameterName, DbType.String, dataColumn.ColumnName, dataRowVersion);
                    break;
                case "System.Int16":
                    _db.AddInParameter(command, parameterName, DbType.Int16, dataColumn.ColumnName, dataRowVersion);
                    break;
                case "System.Int32":
                    _db.AddInParameter(command, parameterName, DbType.Int32, dataColumn.ColumnName, dataRowVersion);
                    break;
                case "System.Int64":
                    _db.AddInParameter(command, parameterName, DbType.Int64, dataColumn.ColumnName, dataRowVersion);
                    break;
                case "System.Short":
                    _db.AddInParameter(command, parameterName, DbType.Int16, dataColumn.ColumnName, dataRowVersion);
                    break;
                case "System.Double":
                    _db.AddInParameter(command, parameterName, DbType.Double, dataColumn.ColumnName, dataRowVersion);
                    break;
                case "System.Decimal":
                    _db.AddInParameter(command, parameterName, DbType.Decimal, dataColumn.ColumnName, dataRowVersion);
                    break;
                case "System.Boolean":
                    _db.AddInParameter(command, parameterName, DbType.Boolean, dataColumn.ColumnName, dataRowVersion);
                    break;
                default :
                    return false;    
            }               
            return true;
        }

        /// <summary>
        /// Attaches the auto increment parameter.
        /// </summary>
        /// <param name="command">The command.</param>
        /// <param name="parameterName">Name of the parameter.</param>
        /// <param name="dataColumn">The data column.</param>
        private void AttachAutoIncrementParameter(DbCommand command, string parameterName, DataColumn dataColumn)
        {
            _db.AddParameter(command ,parameterName, DbType.Int32, ParameterDirection.Output, dataColumn.ColumnName, DataRowVersion.Current, 0);
        }

        private void AssignParameters(DataTable dataTable, IDataParameterCollection parameters)
        {
            string columnName = string.Empty;
            foreach (IDataParameter parameter in parameters)
            {
                if (parameter.Direction!= ParameterDirection.ReturnValue)
                {
                    columnName = GetColumnName(parameter.ParameterName);                    
                    if (dataTable.Columns.Contains(columnName))
                    {
                        parameter.SourceColumn = columnName;
                        if (NeedOrignalVersion(parameter.ParameterName))
                        {
                            parameter.SourceVersion = DataRowVersion.Original;
                        }
                        else
                        {
                            parameter.SourceVersion = DataRowVersion.Current;
                        }
                    }                   
                }
            }
        }

        
        #endregion
        #endregion

        #region internal Method
        // <summary>
        // Accept dataset change.
        // </summary>
        //internal void Finish()
        //{
        //    originalDataSet.Clear();
        //    originalDataSet.Merge(dataSet);           
        //}
        #endregion
    }
}